﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Library.SQL
{
    public class SQLScript
    {
        public static string SelectUserById()
        {
            return "Select * from lib_user where Id = ?Id";
        }
        public static string SelectUserByCode()
        {
            return "Select * from lib_user where Code = ?Code";
        }

        public static string SelectAllUser()
        {
            return "Select * from lib_user";
        }
        public static string SelectMaxUserId()
        {
            return "Select max(Id) from lib_user";
        }
        public static string InsertUser()
        {
            return @"INSERT INTO lib_user
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId
           ,Password
           ,Active)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId, 
           ?Password,
           ?Active)";
        }
        public static string UpdateUser()
        {
            return @"Update lib_user set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
           ,Password= ?Password
           ,Active= ?Active
            Where Id=?Id";
        }

        internal static string DeleteUser()
        {
            return @"delete from lib_user Where Id=?Id";
        }

        internal static string SelectBookCategoryById()
        {
            return "Select * from lib_bookcategory where Id = ?Id";
        }

        internal static string SelectBookCategoryByCode()
        {
            return "Select * from lib_bookcategory where Code = ?Code";
        }

        internal static string SelectAllBookCategory()
        {
            return "Select * from lib_bookcategory";
        }

        internal static string InsertBookCategory()
        {
            return @"INSERT INTO lib_bookcategory
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId)";
        }

        internal static string SelectMaxBookCategoryId()
        {
            return "Select max(Id) from lib_bookcategory";
        }

        internal static string UpdateBookCategory()
        {
            return @"Update lib_bookcategory set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            Where Id=?Id";
        }

        internal static string DeleteBookCategory()
        {
            return @"delete from lib_bookcategory Where Id=?Id";
        }

        internal static string SelectMemberTypeById()
        {
            return "Select * from lib_membertype where Id = ?Id";
        }

        internal static string SelectMemberTypeByCode()
        {
            return "Select * from lib_membertype where Code = ?Code";
        }

        internal static string SelectAllMemberType()
        {
            return "Select * from lib_membertype";
        }

        internal static string SelectAllMemberByLike(string p)
        {
            return "Select * from lib_member where CONCAT(Code,Name,Remark) like '%" + p + @"%' ";
        }

        internal static string InsertMemberType()
        {
            return @"INSERT INTO lib_membertype
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId)";
        }

        internal static string SelectMaxMemberTypeId()
        {
            return "Select max(Id) from lib_membertype";
        }

        internal static string UpdateMemberType()
        {
            return @"Update lib_membertype set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            Where Id=?Id";
        }

        internal static string DeleteMemberType()
        {
            return @"delete from lib_membertype Where Id=?Id";
        }

        internal static string SelectBookShelfById()
        {
            return "Select * from lib_bookshelf where Id = ?Id";
        }

        internal static string SelectBookShelfByCode()
        {
            return "Select * from lib_bookshelf where Code = ?Code";
        }

        internal static string SelectAllBookShelf()
        {
            return "Select * from lib_bookshelf";
        }

        internal static string InsertBookShelf()
        {
            return @"INSERT INTO lib_bookshelf
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId)";
        }

        internal static string SelectMaxBookShelfId()
        {
            return "Select max(Id) from lib_bookshelf";
        }

        internal static string UpdateBookShelf()
        {
            return @"Update lib_bookshelf set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            Where Id=?Id";
        }

        internal static string DeleteBookShelf()
        {
            return @"delete from lib_bookshelf Where Id=?Id";
        }

        internal static string SelectMemberClassById()
        {
            return "Select * from lib_memberclass where Id = ?Id";
        }

        internal static string SelectMemberClassByCode()
        {
            return "Select * from lib_memberclass where Code = ?Code";
        }

        internal static string SelectAllMemberClass()
        {
            return "Select * from lib_memberclass";
        }

        internal static string InsertMemberClass()
        {
            return @"INSERT INTO lib_memberclass
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId)";
        }

        internal static string SelectMaxMemberClassId()
        {
            return "Select max(Id) from lib_memberclass";
        }

        internal static string UpdateMemberClass()
        {
            return @"Update lib_memberclass set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            Where Id=?Id";
        }

        internal static string DeleteMemberClass()
        {
            return @"delete from lib_memberclass Where Id=?Id";
        }

        internal static string SelectMemberById()
        {
            return "Select * from lib_member where Id = ?Id";
        }

        internal static string SelectMemberByCode()
        {
            return "Select * from lib_member where Code = ?Code";
        }

        internal static string SelectAllMember()
        {
            return "Select * from lib_member";
        }

        internal static string InsertMember()
        {
            return @"INSERT INTO lib_member
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId
            ,MemberTypeId
           ,JoinDate
           ,Gender
           ,Address
           ,Phone
           ,IDCardNo
           ,IDCardType
           ,MaxBorrow
           ,MemberClassId)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId
           ,?MemberTypeId
           ,?JoinDate
           ,?Gender
           ,?Address
           ,?Phone
           ,?IDCardNo
           ,?IDCardType
           ,?MaxBorrow
           ,?MemberClassId)";
        }

        internal static string SelectMaxMemberId()
        {
            return "Select max(Id) from lib_member";
        }

        internal static string UpdateMember()
        {
            return @"Update lib_member set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            ,MemberTypeId=?MemberTypeId
           ,JoinDate=?JoinDate
           ,Gender=?Gender
           ,Address=?Address
           ,Phone=?Phone
           ,IDCardNo=?IDCardNo
           ,IDCardType=?IDCardType
           ,MaxBorrow=?MaxBorrow
           ,MemberClassId=?MemberClassId
            Where Id=?Id";
        }

        internal static string DeleteMember()
        {
            return @"delete from lib_member Where Id=?Id";
        }

        internal static string SelectBookById()
        {
            return "Select * from lib_book where Id = ?Id";
        }

        internal static string SelectBookByCode()
        {
            return "Select * from lib_book where Code = ?Code";
        }

        internal static string SelectAllBook()
        {
            return "Select * from lib_book";
        }

        internal static string SelectAllBookByLike(string p)
        {
            return "Select * from lib_book where CONCAT(Code,Name,Remark,Barcode,Author,Publisher,DDC,ISBN)  like '%" + p + @"%' ";
        }

        internal static string InsertBook()
        {
            return @"INSERT INTO lib_book
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId
            ,BookShelfId
           ,BookCategoryId
           ,Barcode
           ,Author
           ,Publisher
           ,DDC
           ,ISBN)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId,
            ?BookShelfId
           ,?BookCategoryId
           ,?Barcode
           ,?Author
           ,?Publisher
           ,?DDC
           ,?ISBN)";
        }

        internal static string SelectMaxBookId()
        {
            return "Select max(Id) from lib_book";
        }

        internal static string UpdateBook()
        {
            return @"Update lib_book set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            ,BookShelfId=?BookShelfId
           ,BookCategoryId=?BookCategoryId
           ,Barcode=?Barcode
           ,Author=?Author
           ,Publisher=?Publisher
           ,DDC=?DDC
           ,ISBN=?ISBN
            Where Id=?Id";
        }

        internal static string DeleteBook()
        {
            return @"delete from lib_book Where Id=?Id";
        }

        internal static string SelectBookStockByBookId()
        {
            return @"select * from lib_bookstock where BookId = ?BookId";
        }

        internal static string InsertBookStock()
        {
            return @"INSERT INTO lib_bookstock
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId,BookId,BookBalance)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId,?BookId,?BookBalance)";

        }

        internal static string SelectMaxBookStockId()
        {
            return "Select max(Id) from lib_bookstock";
        }

        internal static string SelectBookTransactionById()
        {
            return "Select * from lib_booktransaction where Id = ?Id";
        }

        internal static string SelectBookTransactionByCode()
        {
            return "Select * from lib_booktransaction where Code = ?Code";
        }

        internal static string SelectAllBookTransaction()
        {
            return "Select * from lib_booktransaction";
        }

        internal static string InsertBookTransaction()
        {
            return @"INSERT INTO lib_booktransaction
           (Code
           ,Name
           ,Remark
           ,CreatedDate
           ,ModifiedDate
           ,CreatedUserId
           ,ModifiedUserId
            ,BookStockId  
           ,TransactionDate
           ,TransactionType
           ,BorrowDate
           ,ReturnDate
           ,BorrowId
           ,Quantity
           ,MemberId
           ,BookId
)
     VALUES
           (?Code,
           ?Name, 
           ?Remark,
           ?CreatedDate, 
           ?ModifiedDate, 
           ?CreatedUserId,
           ?ModifiedUserId
            ,?BookStockId  
           ,?TransactionDate
           ,?TransactionType
           ,?BorrowDate
           ,?ReturnDate
           ,?BorrowId
           ,?Quantity
           ,?MemberId
           ,?BookId)";
        }

        internal static string SelectMaxBookTransactionId()
        {
            return "Select max(Id) from lib_booktransaction";
        }

        internal static string UpdateBookTransaction()
        {
            return @"Update lib_booktransaction set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            ,BookStockId=?BookStockId  
           ,TransactionDate=?TransactionDate
           ,TransactionType=?TransactionType
           ,BorrowDate=?BorrowDate
           ,ReturnDate=?ReturnDate
           ,BorrowId=?BorrowId
           ,Quantity=?Quantity
           ,MemberId=?MemberId
           ,BookId=?BookId
            Where Id=?Id";
        }

        internal static string DeleteBookTransaction()
        {
            return @"delete from lib_booktransaction Where Id=?Id";
        }

        internal static string SelectBookTransactionByBookId()
        {
            return "Select * from lib_booktransaction where Id = ?BookId";
        }

        internal static string UpdateBookStock()
        {
            return @"Update lib_bookstock set 
           Code = ?Code
           ,Name= ?Name
           ,Remark= ?Remark
           ,ModifiedDate= ?ModifiedDate
           ,ModifiedUserId= ?ModifiedUserId
            ,BookId=?BookId
            ,BookBalance=?BookBalance
            Where Id=?Id";
        }

        internal static string SelectAllBookTransactionByTransactionTypeAndDate()
        {
            return "Select * from lib_booktransaction where TransactionDate = ?TransactionDate and TransactionType=?TransactionType";
        }

        internal static string SelectAllBookTransactionByTransactionTypeAndDateAndBookCode(string p)
        {
            return @"Select t.* from lib_booktransaction t
		JOIN lib_book p on p.Id = t.BookId
		LEFT OUTER JOIN	lib_member m on m.Id = t.MemberId  
            where 
            t.TransactionDate = ?TransactionDate
            and t.TransactionType=?TransactionType 
            and CONCAT(p.Code,p.Name,p.Remark,p.Barcode,p.Author,p.Publisher,p.DDC,p.ISBN)
            like '%" + p + "%' order by t.TransactionDate";
        }

        internal static string SelectAllBookTransactionByTransactionTypeAndDateAndBookCodeMember(string p)
        {
            return @"Select t.* from lib_booktransaction t
		JOIN lib_book p on p.Id = t.BookId
		JOIN	lib_member m on m.Id = t.MemberId  
            where 
            t.TransactionDate = ?TransactionDate
            and t.TransactionType=?TransactionType 
            and CONCAT(p.Code,p.Name,p.Remark,p.Barcode,p.Author,p.Publisher,p.DDC,p.ISBN,m.Code,m.Name)
            like '%" + p + "%'  order by t.TransactionDate";
        }

        internal static string SelectAllBookTransactionByTransactionTypeAndBookCode(string p)
        {
            return @"Select t.* from lib_booktransaction t
		JOIN lib_book p on p.Id = t.BookId
		LEFT OUTER JOIN	lib_member m on m.Id = t.MemberId  
            where 
           t.TransactionType=?TransactionType and 
            CONCAT(p.Code,p.Name,p.Remark,p.Barcode,p.Author,p.Publisher,p.DDC,p.ISBN)
            like '%" + p + "%'  order by t.TransactionDate";
        }

        internal static string SelectAllBookTransactionByTransactionTypeAndBookCodeMember(string p)
        {
            return @"Select t.* from lib_booktransaction t
		JOIN lib_book p on p.Id = t.BookId
		JOIN	lib_member m on m.Id = t.MemberId  
            where 
            t.TransactionType=?TransactionType 
            and CONCAT(p.Code,p.Name,p.Remark,p.Barcode,p.Author,p.Publisher,p.DDC,p.ISBN,m.Code,m.Name)
            like '%" + p + "%'  order by t.TransactionDate";
        }

        internal static string SelectAllBookTransactionByTransactionTypeAndMember()
        {
            return "Select * from lib_booktransaction where MemberId = ?MemberId and TransactionType=?TransactionType";
        }

        internal static string SelectCountBorrowBook()
        {
            return "Select count(*) from lib_booktransaction where BorrowId = ?BorrowId and TransactionType=?TransactionType";           
        }

        internal static string InsertSettings()
        {
            return @"INSERT INTO lib_settings
           (SettingName
           ,setting1
           ,setting2
           ,setting3)
     VALUES
           (?SettingName
           ,?setting1
           ,?setting2
           ,?setting3)";
        }
        internal static string SelectSettings()
        {
            return @"Select * from lib_settings where SettingName = ?SettingName";
        }
        internal static string DeleteSettings()
        {
            return @"Delete from lib_settings where SettingName = ?SettingName";
        }
    }
}